﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;


namespace LimsInfoManage
{
    /// <summary>
    /// 说　　明:数据库操作类
    /// 方法列表:1:CmdClear()                 清空数据库操作语句
    ///          2:CmdAppend(string strnewCmd)追加数据库操作语句
    ///          3:DBOperate(string strSuccess, string strFailed) 数据库插入操作,数据库删除操作,数据库修改操作
    ///          4:Search(DataSet MyDataSet)  通用数据库查询类
    ///          5:bSearch()                  无回显数据库查询操作,记录存在性判断
    ///          6:iSearch()                  无回显数据库查询操作,整形
    ///          7:fSearch()                  无回显数据库查询操作,浮点型
    ///          8:strSearch()                无回显数据库查询操作,字符串类型
    ///          9:cmbSearch(ComboBox cmb)    有回显数据库查询操作,设置combox选择项
    ///         10:dgvSearch(DataGridView dgv)有回显数据库查询操作,输出查询结果
    /// 作　　者:陈小虎
    /// 日　　期:2007-8-14
    /// </summary>
    class DBManage
    {
        //定义私有数据库操作语句序列
        private ArrayList alCmd = new ArrayList();

        private string MyConn = "";
        private SqlConnection MyConnection;
        private SqlCommand MyCommand;
        private SqlTransaction myTrans;

        //构造方法:读取数据库连接字符串
        public DBManage()
        {
            MyConn = GetDBConnStr(Path.GetDirectoryName(Application.ExecutablePath) + "\\dbconfig.txt");
            if (MyConn == "")
            {
                MessageBox.Show("读取数据库连接信息失败");
            }
        }

        //清空数据库操作语句
        public void CmdClear()
        {
            alCmd.Clear();
        }

        //追加数据库操作语句
        public void CmdAppend(string strnewCmd)
        {
            alCmd.Add(strnewCmd + ";");
        }

        //数据库插入操作
        //数据库删除操作
        //数据库修改操作
        //分为显示成功信息和不显示成功信息两种模式.
        public bool DBOperate(string strSuccess, string strFailed)
        {
            try
            {
                MyConnection = new SqlConnection(MyConn);
                MyConnection.Open();
                myTrans = MyConnection.BeginTransaction();
                MyCommand = new SqlCommand();
                MyCommand.Connection = MyConnection;
                MyCommand.Transaction = myTrans;

                for (int i = 0; i < alCmd.Count; i++)
                    MyCommand.CommandText = MyCommand.CommandText + alCmd[i].ToString();
                MyCommand.ExecuteNonQuery();

                myTrans.Commit();              //提交事务
            }
            catch
            {
                myTrans.Rollback();            //事务回滚
                if (strFailed != "")           //失败字符串为空，不显示失败信息
                    MessageBox.Show(strFailed);
                return false;
            }
            finally
            {
                if (MyConnection != null)
                    MyConnection.Close();
            }

            if (strSuccess != "")              //成功字符串为空，不显示成功信息
                MessageBox.Show(strSuccess);
            return true;
        }
        //通用数据库查询类
        private bool Search(DataSet MyDataSet)
        {
            try
            {
                MyConnection = new SqlConnection(MyConn);
                SqlDataAdapter SelectAdapter = new SqlDataAdapter();
                SelectAdapter.SelectCommand = new SqlCommand(alCmd[0].ToString(), MyConnection);
                MyConnection.Open();
                SelectAdapter.SelectCommand.ExecuteNonQuery();
                MyConnection.Close();
                SelectAdapter.Fill(MyDataSet);
                return true;
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.ToString());
                return false;
            }
        }

        //无回显数据库查询操作,记录存在性判断
        public int iExistSearch(string strExist, string strNotExits, string strError)
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            if (bFlag == false)
            {
                if (strError != "")
                    MessageBox.Show(strError);
                return -1;
            }
            if (bFlag == true && MyDataSet.Tables[0].Rows.Count <= 0)
            {
                if (strNotExits != "")
                    MessageBox.Show(strNotExits);
                return 0;
            }

            if (strExist != "")
                MessageBox.Show(strExist);
            return 1;
        }

        //无回显数据库查询操作,整形
        public int iSearch()
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            if (bFlag == false)
                return int.MinValue;
            if (bFlag == true && MyDataSet.Tables[0].Rows.Count <= 0)
                return -1;
            try
            {
                return int.Parse(MyDataSet.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return int.MinValue;
            }
        }

        //无回显数据库查询操作,浮点型
        public float fSearch()
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            if (bFlag == false)
                return float.MinValue;
            if (bFlag == true && MyDataSet.Tables[0].Rows.Count <= 0)
                return -1;
            try
            {
                return float.Parse(MyDataSet.Tables[0].Rows[0].ItemArray[0].ToString());
            }
            catch
            {
                return float.MinValue;
            }
        }

        //无回显数据库查询操作,字符串类型
        public string strSearch()
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            if (bFlag == false)
                return string.Empty;
            if (bFlag == true && MyDataSet.Tables[0].Rows.Count <= 0)
                return string.Empty;
            try
            {
                return MyDataSet.Tables[0].Rows[0].ItemArray[0].ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        //有回显数据库查询操作,设置combox选择项
        public bool cmbSearch(ComboBox cmb)
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            //cmb.Items.Clear();

            if (bFlag == false)
                return false;
            if (MyDataSet.Tables[0].Rows.Count > 0)
            {
                int i;
                for (i = 0; i < MyDataSet.Tables[0].Rows.Count; i++)
                    cmb.Items.Add(MyDataSet.Tables[0].Rows[i].ItemArray[0].ToString());
                cmb.SelectedIndex = 0;
            }
            return true;
        }

        //有回显数据库查询操作,输出查询结果
        public bool dgvSearch(DataGridView dgv)
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            dgv.DataSource = null;
            if (bFlag == false)
                return false;
            dgv.DataSource = MyDataSet.Tables[0];
            return true;
        }

        //获取行纪录
        public bool LineSearch(ArrayList al)
        {
            DataSet MyDataSet = new DataSet();
            al.Clear();
            bool bFlag = Search(MyDataSet);
            if (bFlag == false)
                return false;
            if (MyDataSet.Tables[0].Rows.Count > 0)
            {
                int i;
                for (i = 0; i < MyDataSet.Tables[0].Columns.Count; i++)
                    al.Add(MyDataSet.Tables[0].Rows[0].ItemArray[i].ToString());
            }
            return true;
        }

        //获取表纪录
        public bool TableSearch(ArrayList al)
        {
            DataSet MyDataSet = new DataSet();
            bool bFlag = Search(MyDataSet);
            if (bFlag == false)
                return false;
            if (MyDataSet.Tables[0].Rows.Count > 0)
            {
                int i, j;
                for (i = 0; i < MyDataSet.Tables[0].Rows.Count; i++)
                    for (j = 0; j < MyDataSet.Tables[0].Columns.Count; j++)
                        al.Add(MyDataSet.Tables[0].Rows[i].ItemArray[j].ToString());
            }
            return true;
        }

        //获取数据库连接字符串
        public string GetDBConnStr(string strConfigFileName)
        {
            string strRet = "";

            TextReader tr = new StreamReader(strConfigFileName);

            int NumberOfLines = 5;

            string[] ListLines = new string[NumberOfLines];

            for (int i = 0; i < NumberOfLines; i++)
            {
                ListLines[i] = tr.ReadLine();
            }

            tr.Close();

            strRet = ListLines[0] + ListLines[1] + ListLines[2] + ListLines[3] + ListLines[4];
            return strRet;

        }
    }
}
